﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

/// <summary>
/// 文件名：ClsIntoAffirmDAL.cs
/// 作  者：Kidy
/// 时  间：2006.11.28 CREATE
///         2006.12.11 UPDATE
/// 说  明：ClsIntoAffirmDAL类用于入库确认信息相关处理
public class ClsIntoAffirmDAL
{
    /// <summary>
    /// 数据库连接字符串
    /// </summary>
    private static string strSqlConnectionString = ConfigurationManager.AppSettings["SQLCONNECTIONSTRING"];

    /// <summary>
    /// 获取所有入库确认主表信息
    /// </summary>
    /// <param name="Co_ID">公司ID</param>
    /// <param name="User_ID">用户ID</param>
    /// <param name="Ds_ID">数据集ID</param>
    /// <returns>返回入库确认主表信息数据集</returns>
    public DataSet GetIntoAffirmMains(string Co_ID, string User_ID, string Ds_ID)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetDataSetIntoAffirm", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        //dat.SelectCommand.Parameters.Add("@co_id", Co_ID);
        //dat.SelectCommand.Parameters.Add("@user_id", User_ID);
        //dat.SelectCommand.Parameters.Add("@ds_id", Ds_ID);

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 获取所有入库确认盘点从表信息
    /// </summary>
    /// <returns>返回入库确认从表信息数据集</returns>
    public DataSet GetIntoAffirmChilds(string sInto_no,string sType)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetIntoAffirmChildD", con);//Pro_GetIntoAffirmChilds

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@Into_no", sInto_no);
        dat.SelectCommand.Parameters.Add("@type", sType);

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    ///// <summary>
    ///// 获取所有采购从表信息
    ///// <param name="Tm_On">盘点单号</param>
    ///// </summary>
    ///// <returns>返回退货从表信息数据集</returns>
    //public DataSet GetIntoValidateChilds(string Dm_No, string sType)
    //{
    //    DataSet dst = new DataSet();
    //    SqlConnection con = new SqlConnection(strSqlConnectionString);
    //    SqlDataAdapter dat = new SqlDataAdapter("Pro_GetStockOrderChilds", con);//Pro_GetSellBackChilds

    //    dat.SelectCommand.CommandType = CommandType.StoredProcedure;
    //    dat.SelectCommand.Parameters.Add("@dm_no", Dm_No);
    //    dat.SelectCommand.CommandType = CommandType.StoredProcedure;
    //    dat.SelectCommand.Parameters.Add("@type", sType);

    //    try
    //    {
    //        dat.Fill(dst);
    //    }
    //    catch
    //    {
    //        dst = null;
    //    }
    //    finally
    //    {
    //        if (con.State == ConnectionState.Open)
    //        {
    //            con.Close();
    //        }
    //    }

    //    return dst;
    //}

    /// <summary>
    /// 增加采购入库确认信息
    /// </summary>
    /// <param>采购入库确认编号</param>
    /// <param>......</param>
    /// <returns>返回修改是否成功</returns>
    /// strDm_no,strDm_date,strCt_name,strDm_money,strDm_Cancel,strDm_Stop,strDm_Writer,strCr_Remark
    public Boolean AddIntoAffirm(string sDm_no, DateTime dtDate, string sCt_id, decimal dDm_money, int iDm_Cancel, int iDm_Stop, string sDm_Writer, string sRemark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddIntoAffirm", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@Dm_no", SqlDbType.NVarChar, 20);
        com.Parameters["@Dm_no"].Value = sDm_no;
        com.Parameters.Add("@dDate", SqlDbType.DateTime, 8);
        com.Parameters["@dDate"].Value = dtDate;
        com.Parameters.Add("@Ct_id", SqlDbType.NVarChar, 60);
        com.Parameters["@Ct_id"].Value = sCt_id;
        com.Parameters.Add("@Dm_money", SqlDbType.Decimal, 28);
        com.Parameters["@Dm_money"].Value = dDm_money;
        com.Parameters.Add("@Dm_Cancel", SqlDbType.Int, 1);
        com.Parameters["@Dm_Cancel"].Value = iDm_Cancel;
        com.Parameters.Add("@Dm_Stop", SqlDbType.Int, 1);
        com.Parameters["@Dm_Stop"].Value = iDm_Stop;
        com.Parameters.Add("@Dm_Writer", SqlDbType.NVarChar, 20);
        com.Parameters["@Dm_Writer"].Value = sDm_Writer;
        com.Parameters.Add("@Remark", SqlDbType.NVarChar, 255);
        com.Parameters["@Remark"].Value = sRemark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    //****************************************入库确认****************************************
    /// <summary>
    /// 增加入库确认信息
    /// </summary>
    /// <param>采购入库确认编号</param>
    /// <param>......</param>
    /// <returns>返回修改是否成功</returns>
    /// strDm_no, dtDm_date,strStockDm_no,dtStockDm_date, strPr_id, dDm_money, 
    ///iDm_Validate,strDm_Writer, strDm_Valier, strCr_Remark
    public Boolean AddIntoValid(string sType,string sInto_no, DateTime dtInto_date, string strStockDm_no, DateTime dtStockDm_date, string sPr_id, 
        string sDp_id,string sWh_id,decimal dDm_money, int iDm_Validate, string sDm_Writer,string strDm_Valier, string sRemark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddStockIntoValid", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@Type", SqlDbType.NVarChar, 2);
        com.Parameters["@Type"].Value = sType;
        com.Parameters.Add("@Dm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@Dm_no"].Value = sInto_no;
        com.Parameters.Add("@dDate", SqlDbType.DateTime, 8);
        com.Parameters["@dDate"].Value = dtInto_date;
        com.Parameters.Add("@StockDm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@StockDm_no"].Value = strStockDm_no;
        com.Parameters.Add("@StockDm_date", SqlDbType.DateTime, 8);
        com.Parameters["@StockDm_date"].Value = dtStockDm_date;
        com.Parameters.Add("@Pr_id", SqlDbType.NVarChar, 60);
        com.Parameters["@Pr_id"].Value = sPr_id;
        com.Parameters.Add("@Dp_id", SqlDbType.NVarChar, 30);
        com.Parameters["@Dp_id"].Value = sDp_id;
        com.Parameters.Add("@Wh_id", SqlDbType.NVarChar, 30);
        com.Parameters["@Wh_id"].Value = sWh_id;
        com.Parameters.Add("@Dm_money", SqlDbType.Decimal, 28);
        com.Parameters["@Dm_money"].Value = dDm_money;
        com.Parameters.Add("@Dm_Validate", SqlDbType.Int, 1);
        com.Parameters["@Dm_Validate"].Value = iDm_Validate;
        com.Parameters.Add("@Dm_Writer", SqlDbType.NVarChar, 20);
        com.Parameters["@Dm_Writer"].Value = sDm_Writer;
        com.Parameters.Add("@Dm_Valier", SqlDbType.NVarChar, 20);
        com.Parameters["@Dm_Valier"].Value = strDm_Valier;
        com.Parameters.Add("@Remark", SqlDbType.NVarChar, 255);
        com.Parameters["@Remark"].Value = sRemark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 增加采购入库确认明细信息
    /// </summary>
    /// <param>采购入库确认编号</param>
    /// <param>......</param>
    /// <returns>返回修改是否成功</returns>
    public Boolean AddChildIntoAffirm(string sDm_no, string sIm_id, int iChildAcount, decimal dChildPrice, string sRemark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddIntoAffirmD", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@Dm_no", SqlDbType.NVarChar, 20);
        com.Parameters["@Dm_no"].Value = sDm_no;
        com.Parameters.Add("@Im_id", SqlDbType.NVarChar, 20);
        com.Parameters["@Im_id"].Value = sIm_id;
        com.Parameters.Add("@ChildAcount", SqlDbType.Int, 4);
        com.Parameters["@ChildAcount"].Value = iChildAcount;
        com.Parameters.Add("@ChildPrice", SqlDbType.Decimal, 28);
        com.Parameters["@ChildPrice"].Value = dChildPrice;
        com.Parameters.Add("@Remark", SqlDbType.NVarChar, 255);
        com.Parameters["@Remark"].Value = sRemark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 作废采购入库确认信息
    /// </summary>
    /// <param>采购入库确认编号</param>
    /// <param name="Is_Visible">作废状态1为临时作废，－1为彻底作废</param>
    /// <returns>返回作废是否成功</returns>
    public Boolean DeleteIntoAffirm(string sType, string sInto_no, int iIs_Visible)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_DeleteIntoAffirm", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@type", SqlDbType.NVarChar, 2);
        com.Parameters["@type"].Value = sType;
        com.Parameters.Add("@Into_no", SqlDbType.NVarChar, 30);
        com.Parameters["@Into_no"].Value = sInto_no;
        com.Parameters.Add("@Is_Visible", SqlDbType.Int, 1);
        com.Parameters["@Is_Visible"].Value = iIs_Visible;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 作废入库确认、退货确认明细表记录 
    /// </summary>
    /// <param>CREATE BY KIDY ON 20061228</param>
    /// <param name="Is_Visible">作废状态1为临时作废，－1为彻底作废</param>
    /// <returns>返回作废是否成功</returns>
    public Boolean DeleteChildIntoAffirm(string sType,string sInto_no, string sIm_no, string sLot_no,int iState)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_DeleteChildIntoAffirm", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@type", SqlDbType.NVarChar, 30);
        com.Parameters["@type"].Value = sType;
        com.Parameters.Add("@Into_no", SqlDbType.NVarChar, 30);
        com.Parameters["@Into_no"].Value = sInto_no;
        com.Parameters.Add("@Im_no", SqlDbType.NVarChar, 30);
        com.Parameters["@Im_no"].Value = sIm_no;
        com.Parameters.Add("@lot_no", SqlDbType.NVarChar, 30);
        com.Parameters["@lot_no"].Value = sLot_no;
        com.Parameters.Add("@state", SqlDbType.Int, 1);
        com.Parameters["@state"].Value = iState;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 确认采购入库确认信息
    /// </summary>
    /// <param>采购入库确认编号</param>
    /// <param name="AuditStat">确认状态</param>
    /// <param name="Auditor">确认人</param>
    /// <returns>返回确认是否成功</returns>
    public Boolean AuditStatIntoAffirm(string ID, int AuditStat, string Auditor)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AuditIntoAffirm", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@dm_no", SqlDbType.NVarChar, 20);
        com.Parameters["@dm_no"].Value = ID;
        com.Parameters.Add("@is_audit", SqlDbType.Int);
        com.Parameters["@is_audit"].Value = AuditStat;
        com.Parameters.Add("@auditor", SqlDbType.NVarChar, 60);
        com.Parameters["@auditor"].Value = Auditor;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 修改入库确认主表
    /// </summary>
    /// <param>CREATE BY KIDY ON 20061228</param>
    /// <returns>返回修改是否成功</returns>
    ///ctbRsInto_no.Text.Trim(), DateTime.Parse(ctsbRsInto_date.Text.Trim()),ctbRemark.Text.Trim()
    public Boolean UpDateIntoAffirm(string sInto_no, DateTime dInto_date, string sRemark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpDateIntoAffirmM", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@Into_no", SqlDbType.NVarChar, 30);
        com.Parameters["@Into_no"].Value = sInto_no;
        com.Parameters.Add("@Into_Date", SqlDbType.DateTime, 8);
        com.Parameters["@Into_Date"].Value = dInto_date;     
        com.Parameters.Add("@Remark", SqlDbType.Text);
        com.Parameters["@Remark"].Value = sRemark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    //****************************************入库确认****************************************
    /// <summary>
    /// 修改采购入库确认信息
    /// </summary>
    /// <param>采购入库确认编号</param>
    /// <param>......</param>
    /// <returns>返回修改是否成功</returns>
    //ctbRs0.Text.Trim(), ctsbRs0.Text.Trim(), ctsbRs1.Text.Trim(), ctsbRs2.Text.Trim(),
    //int.Parse(drpCancel.SelectedValue), int.Parse(drpStop.SelectedValue), ctsbWriter.Text.Trim(), ctbRemark.Text.Trim()
    public Boolean UpDateIntoAffirmAud(string sDm_no, DateTime dtDm_date, string sCt_id, decimal dDm_SumMoney, int iDm_Audit,
                                string sDm_Writer, string sDm_Auditer, string sRemark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpDateIntoAffirmAudiM", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@Dm_no", SqlDbType.NVarChar, 20);
        com.Parameters["@Dm_no"].Value = sDm_no;
        com.Parameters.Add("@dDate", SqlDbType.DateTime, 8);
        com.Parameters["@dDate"].Value = dtDm_date;
        com.Parameters.Add("@Ct_id", SqlDbType.NVarChar, 60);
        com.Parameters["@Ct_id"].Value = sCt_id;
        com.Parameters.Add("@Dm_money", SqlDbType.Decimal, 28);
        com.Parameters["@Dm_money"].Value = dDm_SumMoney;
        com.Parameters.Add("@Dm_Audit", SqlDbType.Int, 1);
        com.Parameters["@Dm_Audit"].Value = iDm_Audit;
        com.Parameters.Add("@Dm_Auditer", SqlDbType.NVarChar, 20);
        com.Parameters["@Dm_Auditer"].Value = sDm_Auditer;
        com.Parameters.Add("@Dm_Writer", SqlDbType.NVarChar, 20);
        com.Parameters["@Dm_Writer"].Value = sDm_Writer;
        com.Parameters.Add("@Remark", SqlDbType.NVarChar, 255);
        com.Parameters["@Remark"].Value = sRemark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }


    /// <summary>
    /// 修改采购入库确认明细信息
    /// </summary>CREATE BY KIDY ON 2001228
    /// <param></param>
    /// <param></param>
    /// <returns>返回修改是否成功</returns>
    ///strInto_No, ctsbProductId.Text.Trim(),ctbBatchNo.Text.Trim(),ctbQry.Text.Trim(),ctbRemark.Text.Trim()
    public Boolean UpDateIntoAffirmD(int iId,string sInto_no, string sIm_id,string sBatch_no, int iQty, string sRemark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpDateIntoAffirmD", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@id", SqlDbType.Int);
        com.Parameters["@id"].Value = iId;
        com.Parameters.Add("@Into_no", SqlDbType.NVarChar, 20);
        com.Parameters["@Into_no"].Value = sInto_no;
        com.Parameters.Add("@Im_no", SqlDbType.NVarChar, 20);
        com.Parameters["@Im_no"].Value = sIm_id;
        com.Parameters.Add("@Batch_no", SqlDbType.NVarChar, 30);
        com.Parameters["@Batch_no"].Value = sBatch_no;
        com.Parameters.Add("@Qty", SqlDbType.Int);
        com.Parameters["@Qty"].Value = iQty;
        com.Parameters.Add("@Remark", SqlDbType.NVarChar, 255);
        com.Parameters["@Remark"].Value = sRemark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 检查采购入库确认单是否可以确认（商品入库数量不能>采购订单商品剩余数量）
    /// </summary>
    /// <param name="sType"></param>
    /// <param name="sInto_no"></param>
    /// <returns></returns>
    public string ChkIsAudit(string sType,string sInto_no)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_ChkIsAudit", con);
        com.CommandType = CommandType.StoredProcedure;
        SqlParameter ParOutPut = new SqlParameter();

        com.Parameters.Add("@type", SqlDbType.NVarChar, 2);
        com.Parameters["@type"].Value = sType;
        com.Parameters.Add("@Into_no", SqlDbType.NVarChar, 30);
        com.Parameters["@Into_no"].Value = sInto_no;
        ParOutPut = com.Parameters.Add("@reState", SqlDbType.Int);
        ParOutPut.Direction = ParameterDirection.Output;

        try
        {
            con.Open();
            com.ExecuteNonQuery();
            string strReState = com.Parameters["@reState"].Value.ToString();//返回状态（0:成功;1:失败）
            return strReState;
        }
        catch
        {
            return "";
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    
    }

    /// <summary>
    /// 检查 4:采购入库确认，5:采购退货确认 是否可以取消确认：依据（库存中是否有相应记录，并且出库数量小于或等于该商品期末数量）
    /// <returns>CREATE BY KIDY ON 20061212</returns>
    /// </summary>
    public String CheckWhetherAudtit(string sTm_No, string sTm_Type, int iState, string Auditor)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AudittbhTranM_Stock", con);
        com.CommandType = CommandType.StoredProcedure;
        SqlParameter ParOutPut = new SqlParameter();

        com.Parameters.Add("@Into_no", SqlDbType.NVarChar, 30);
        com.Parameters["@Into_no"].Value = sTm_No;
        com.Parameters.Add("@dm_state", SqlDbType.Int, 4);
        com.Parameters["@dm_state"].Value = iState;
        com.Parameters.Add("@auditor", SqlDbType.NVarChar, 60);
        com.Parameters["@auditor"].Value = Auditor;
        com.Parameters.Add("@type", SqlDbType.NVarChar, 2);
        com.Parameters["@type"].Value = sTm_Type;
        ParOutPut = com.Parameters.Add("@reIm_no", SqlDbType.VarChar, 30);
        ParOutPut.Direction = ParameterDirection.Output;
        ParOutPut = com.Parameters.Add("@relot_no", SqlDbType.NVarChar, 30);
        ParOutPut.Direction = ParameterDirection.Output;
        ParOutPut = com.Parameters.Add("@returnState", SqlDbType.NVarChar, 2);
        ParOutPut.Direction = ParameterDirection.Output;
        try
        {
            con.Open();
            com.ExecuteNonQuery();
            string strState = com.Parameters["@returnState"].Value.ToString();//返回状态（N:未入库;Q:库存不足）
            string strReIm_no = com.Parameters["@reIm_no"].Value.ToString();//返回的商品号
            string strReLot_no = com.Parameters["@relot_no"].Value.ToString();//返回的批号
            if (strState == "N")
            {
                return "N" +"|"+strReIm_no+"|"+ strReLot_no;
            }
            else if (strState == "Q")
            {
                return "Q" + "|" + strReIm_no + "|" + strReLot_no;
            }
            else
            {
                return "0";
            }
        }
        catch
        {
            return "";
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }
}
